/**
 * Created by Dell on 2017/4/25.
 */
'use strict';
const pg = require('../lib/pgClient');
const log = require('log4js').getLogger('models/recommend');
const async = require('async');
let sqlStart = "START TRANSACTION;";
let sqlRollback = "ROLLBACK;";
let sqlCommit = "COMMIT;";
/**
 * 后台多条件查询图片列表
 * @param params//查询条件
 * @param args//判断信息
 * @param callback
 */
exports.list_b = function(params, args, callback){
    //let sql = 'SELECT p.*, t.typename FROM picture p, type t WHERE p.tid = t.id';
    let sql = 'select t.typename, p.* from (recommend r left join picture p on p.id = r.pid) left join type t on p.tid = t.id where 1=1';
    let number = 1;
    if(args.type && args.type !='all'){//添加分类条件限制
        sql += ' AND t.typename = $'+number;
        number ++;
    }
    if(args.keyword){//对图片名称、用户名、描述进行模糊查询
        sql +=' AND ( p.name like $'+number+' OR p.username like $'+number+' OR p."desc" like $'+number+' )';
        number ++;
    }
    sql +=' AND p.del = $'+number;
    number++;
    // if(args.sotring){//排序字段（倒序排列）
    //     sql +=' ORDER BY $'+number+' DESC';
    //     number++;
    // }
    if(args.sotring == 'pushtime'){//发布时间（倒序排列）
        sql +=' ORDER BY p.pushtime DESC';
    }
    if(args.sotring == 'want'){//想要（倒序排列）
        sql +=' ORDER BY p.want DESC';
    }
    if(args.sotring == 'own'){//拥有（倒序排列）
        sql +=' ORDER BY p.own DESC';
    }
    sql +=' LIMIT $'+number+' OFFSET $'+(number + 1);
    pg.query(sql, params, function(err, result){
        if(err){//报错
            callback (err, null);
        }else{
            callback (err, result);//sql执行没问题
        }
    });
};
/**
 * 后台多条件查询图片列表数据统计
 * @param params//查询条件
 * @param args//判断信息
 * @param callback
 */
exports.count_b = function(params, args, callback){
    //let sql = 'SELECT p.*, t.typename FROM picture p, type t WHERE p.tid = t.id';
    let sql = 'select t.typename, p.* from (recommend r left join picture p on p.id = r.pid) left join type t on p.tid = t.id where 1=1';
    let number = 1;
    if(args.type && args.type !='all'){//添加分类条件限制
        sql += ' AND t.typename = $'+number;
        number ++;
    }
    if(args.keyword){//对图片名称、用户名、描述进行模糊查询
        sql +=' AND ( p.name like $'+number+' OR p.username like $'+number+' OR p."desc" like $'+number+' )';
        number ++;
    }
    sql +=' AND p.del = $'+number;

    pg.query(sql, params, function(err, result){
        if(err){//报错
            callback (err, null);
        }else{
            callback (err, result);//sql执行没问题
        }
    });
};
/**
 * 后台批量删除推荐作品记录
 * @param args
 * @param callback
 */
exports.remove = function(args, callback){
    let ids = args.pids;//ids 是数组
    let theids = "'"+ids.join("','")+"'";//将数组ids整理成字符串
    const sql1 = 'SELECT * FROM picture WHERE id in ('+theids+') AND del = $1';//是否有已经被删除的图片
    const sql2 = 'UPDATE picture SET recommend = $1 WHERE id in ('+theids+')';//删除图片
    const sql3 = 'DELETE FROM recommend WHERE pid in ('+theids+')';//删除推荐记录
    const params1 = [2];//查是否有不可用图片（被删除的）
    const params2 = [2];//将照片修改为不推荐
    pg.query(sql1, params1, function(err, result){//执行查洵是否有被删除的照片
        if(err){
            callback (err, null);//报错
        }else if(result.rowCount > 0){
            callback (err, 'exit_del');//有被删除的作品
        }else {
            pg.connect(function (err, client, done) {
                client.query(sqlStart, function () {
                    client.query(sql3, [], function (err, result) {//删除推荐记录
                        if (err||result.rowCount == 0) {
                            log.error('删除推荐记录失败');
                            client.query(sqlRollback, function (errRollback) {
                                log.error('回滚');
                                if (errRollback) {
                                    log.error('回滚失败');
                                }
                                done();
                                callback(err, null);
                            })
                        } else {
                            client.query(sql2, params2, function(err, result){//编辑作品推荐状态
                                if(err||result.rowCount == 0){
                                    log.error('编辑图片推荐状态失败');
                                    client.query(sqlRollback, function(errRollback){
                                        log.error('回滚失败');
                                        if(errRollback){
                                            log.error('回滚失败')
                                        }
                                        done();
                                        callback (err, null);
                                    })
                                }else {
                                    client.query(sqlCommit, function (errCommit) {
                                        if (errCommit) {
                                            client.query(sqlRollback, function (errRollback) {
                                                if (errRollback) {
                                                    log.error('回滚失败');
                                                }
                                                done();
                                                callback(err, null);
                                            });
                                        } else {
                                            done();
                                            return callback(err, result);//删除成功
                                        }
                                    });
                                }
                            })
                        }
                    })
                })
            })
        }
    })
};